import sqlite3

dbname = 'test.db'


def creatTables():
    conn = sqlite3.connect(dbname)
    c = conn.cursor()

    # TYPE TABLE
    sql = '''CREATE TABLE IF NOT EXISTS TYPES 
           (TYPE_ID     INTEGER     PRIMARY KEY AUTOINCREMENT ,
           TYPE_NAME    VARCHAR(40) NOT NULL);'''
    c.execute(sql)
    conn.commit()

    # DEVICE TABLE
    sql = '''CREATE TABLE IF NOT EXISTS DEVICES 
           (DEVICE_ID   INTEGER     PRIMARY KEY AUTOINCREMENT,
           DEVICE_NAME  VARCHAR(20)                                 NOT NULL,
           TYPE_ID      INT                                         NOT NULL,
           DEVICE_ADDR  VARCHAR(100)                                NOT NULL );'''
    c.execute(sql)
    conn.commit()

    # CONNECTION TABLE
    sql = '''CREATE TABLE IF NOT EXISTS CONNECTIONS 
               (CONNECTION_ID   INTEGER     PRIMARY KEY AUTOINCREMENT,
               DEVICE_ID        INT            NOT NULL,
               RELATED_TYPE     VARCHAR(20)    NOT NULL,
               RELATED_DEVICE_ID    INT ,
               RELATED_TYPE_ID      INT );'''
    c.execute(sql)
    conn.commit()

    print('Tables create successfully')

    conn.close()


def add_type(type_name):
    type_name = type_name.upper()
    if is_type_exist(type_name):
        print('Such type name already exists! Please check your database. Add type %s fail' % type_name)
        return -1
    conn = sqlite3.connect(dbname)
    c = conn.cursor()

    sql = "INSERT INTO TYPES  VALUES (NULL,'%s')" % str(type_name)
    c.execute(sql)
    conn.commit()
    conn.close()
    return 1


def add_device(device_name, device_type_id, device_address):
    if is_Dname_exist(device_name):
        print('Such device name already exists! Please check your database. Add device %s fail' % device_name)
        return -1
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "INSERT INTO DEVICES \
           VALUES (NULL,'%s','%s','%s')" % (str(device_name), str(device_type_id), str(device_address))
    print(sql)
    c.execute(sql)
    conn.commit()
    conn.close()
    return 1


def add_connection(device_id, related_type, related_id):
    rt = related_type.upper()
    if rt == 'GROUP':
        related_device_id = "NULL"
        related_group_id = "\'" + str(related_id) + "\'"
    elif rt == 'DEVICE':
        related_group_id = "NULL"
        related_device_id = "\'" + str(related_id) + "\'"
    # elif rt == 'GROUPS':
    #     related_device_id = "NULL"
    #     related_group_id = "\'" + str(related_id) + "\'"
    else:
        print('Wrong related rt! Add fail')
        return -1

    if is_conn_exist(device_id, rt, related_device_id, related_group_id):
        print('Such connection already exists! Please check your database. Add connection fail')
        return -1

    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "INSERT INTO CONNECTIONS \
           VALUES (NULL,'%s','%s',%s,%s)" % (str(device_id), str(rt), related_device_id, related_group_id)
    print(sql)
    c.execute(sql)
    conn.commit()
    conn.close()
    return 1


def delete_type(type_id):
    device_in_type = get_device_in_type(type_id)
    if len(device_in_type) > 0:
        return -1
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "DELETE FROM TYPES \
           WHERE TYPE_ID='%s';" % str(type_id)
    print(sql)
    c.execute(sql)
    conn.commit()
    conn.close()
    return 1


def delete_device(device_id):
    conn_list = get_connection_device(device_id)
    print(conn_list)
    if len(conn_list) > 0:
        return -1
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "DELETE FROM DEVICES \
           WHERE DEVICE_ID= '%s';" % str(device_id)
    print(sql)
    c.execute(sql)
    conn.commit()
    conn.close()
    return 1


def delete_connection(connection_id):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "DELETE FROM CONNECTIONS \
           WHERE CONNECTION_ID='%s';" % str(connection_id)
    print(sql)
    c.execute(sql)
    conn.commit()
    conn.close()
    return 1


def modify_device(device_id, device_name, type_id, device_address):
    # if is_Dname_exist(device_name):
    #     print('Such device name already exists! Please check your database. Add device %s fail' % device_name)
    #     return -1

    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "UPDATE DEVICES \
           SET DEVICE_NAME='%s',TYPE_ID='%s',DEVICE_ADDR='%s' \
           WHERE DEVICE_ID='%s';" % (str(device_name), str(type_id), str(device_address), str(device_id))
    # print(sql)
    c.execute(sql)
    conn.commit()
    conn.close()
    return 1


def modify_type(type_id, type_name):
    type_name = type_name.upper()
    if is_type_exist(type_name):
        print('Such type name already exists! Please check your database. Modify type fail')
        return -1
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "UPDATE TYPES \
           SET TYPE_NAME='%s' \
           WHERE TYPE_ID='%s';" % (str(type_name), str(type_id))
    print(sql)
    c.execute(sql)
    conn.commit()
    conn.close()
    return 1


def modify_connection(c_id, device_id, related_type, related_id):
    rt = related_type.upper()
    rt_id = "NULL"
    rd_id = "NULL"
    if rt == 'GROUP':
        rt_id = "\'" + str(related_id) + "\'"
    elif rt == 'DEVICE':
        rd_id = "\'" + str(related_id) + "\'"
    # elif rt == 'GROUPS':
    #     rg_id = "\'" + str(related_id) + "\'"
    else:
        print('Wrong related type ! modify Error')
        return -1

    if is_conn_exist(device_id, rt, rd_id, rt_id):
        print('Such connection already exists! Please check your database. Modify connection fail')
        return -1

    conn = sqlite3.connect(dbname)
    c = conn.cursor()

    sql = "UPDATE CONNECTIONS \
           SET DEVICE_ID='%s', RELATED_TYPE='%s',RELATED_DEVICE_ID=%s, RELATED_GROUP_ID=%s\
           WHERE CONNECTION_ID='%s';" % (str(device_id), str(rt), rd_id, rt_id, str(c_id))
    print(sql)
    c.execute(sql)
    conn.commit()
    conn.close()
    return 1


def get_connection_device(device_id):
    conn = sqlite3.connect(dbname)
    all_connections = set()
    c = conn.cursor()
    sql = "SELECT * FROM CONNECTIONS WHERE DEVICE_ID=%s " % device_id
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    for i in res:
        if i[2] == 'DEVICE':
            all_connections.add(i[3])
        else:
            devices = set(get_device_in_group(i[4]))
            all_connections = all_connections | devices
    return list(all_connections)


def get_device_in_group(group_id):
    conn = sqlite3.connect(dbname)
    all_devices = set()
    c = conn.cursor()
    sql = "SELECT DEVICE_IDS FROM GROUPS WHERE GROUP_ID=%s " % group_id
    c.execute(sql)
    res = c.fetchone()
    conn.close()
    print(res)
    devices = str(res[0]).split(',')
    for device in devices:
        all_devices.add(int(device))
    return list(all_devices)


def get_device_in_type(type_id):
    conn = sqlite3.connect(dbname)
    all_devices = set()
    c = conn.cursor()
    sql = "SELECT DEVICE_ID FROM DEVICES WHERE TYPE_ID=%s " % type_id
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    for i in res:
        all_devices.add(i[0])
    return list(all_devices)


def get_addr(device_id):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT DEVICE_ADDR FROM DEVICES WHERE DEVICE_ID=%s " % device_id
    c.execute(sql)
    res = c.fetchone()
    conn.close()
    return res[0]


# 根据设备id获取设备信息
def get_device(device_id):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT * FROM DEVICES WHERE DEVICE_ID=%s " % device_id
    c.execute(sql)
    res = c.fetchone()
    conn.close()
    return res


def get_device_id(device_name):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT * FROM DEVICES WHERE DEVICE_NAME='%s' " % device_name
    # print(sql)
    c.execute(sql)
    res = c.fetchone()
    conn.close()
    if res is not None:
        return res[0]
    return None


def is_Dname_exist(name):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT * FROM DEVICES WHERE DEVICE_NAME='%s'" % name
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    if len(res) > 0:
        return True
    return False


def is_type_exist(name):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT * FROM TYPES WHERE TYPE_NAME='%s'" % name
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    if len(res) > 0:
        return True
    return False


def get_device_info(device_id):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT * FROM DEVICES WHERE DEVICE_ID='%s'" % device_id
    c.execute(sql)
    res = c.fetchone()
    conn.close()
    return res


def get_type_info(type_id):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT * FROM TYPES WHERE TYPE_ID='%s'" % type_id
    c.execute(sql)
    res = c.fetchone()
    conn.close()
    return res


def show_type():
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT * FROM TYPES "
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    return res


def show_device():
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT * FROM DEVICES "
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    return res


def get_connection_info(connection_id):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT * FROM CONNECTIONS WHERE CONNECTION_ID='%s'" % connection_id
    c.execute(sql)
    res = c.fetchone()
    conn.close()
    return res


def show_connection():
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT * FROM CONNECTIONS "
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    return res


def is_conn_exist(device_id, rt, related_device_id, related_group_id):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    if rt == 'DEVICE':
        sql = "SELECT * FROM CONNECTIONS \
               WHERE DEVICE_ID='%s'\
               AND RELATED_TYPE='%s' \
               AND RELATED_DEVICE_ID=%s \
               AND RELATED_GROUP_ID IS NULL" % (str(device_id), str(rt), related_device_id)
    else:
        sql = "SELECT * FROM CONNECTIONS \
               WHERE DEVICE_ID='%s'\
               AND RELATED_TYPE='%s' \
               AND RELATED_DEVICE_ID IS NULL \
               AND RELATED_GROUP_ID = %s" % (str(device_id), str(rt), related_group_id)
    # print(sql)
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    if len(res) > 0:
        return True
    else:
        return False


def show_executionLog():
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT * FROM EXECUTIONLOG ORDER BY IMPLEMENT_TIME desc"
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    return res


def show_executionLog_paging(searchText, pageIndex, pageSize):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT * FROM EXECUTIONLOG"
    if searchText:
        sql += " WHERE SENDER_NAME LIKE '%%%s%%' OR TASK_ID = '%s'" % (searchText, searchText)
    sql += " ORDER BY IMPLEMENT_TIME desc LIMIT %s, %s" % (pageIndex, pageSize)
    # print(sql)
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    return res


def add_executionLog(taskId, senderId, senderName, interface, addresseeId, addresseeName, statusCode, describe,
                     implementTime):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()

    taskId = 'NULL' if not taskId else "\'" + taskId + "\'"
    senderId = 'NULL' if not senderId else senderId
    senderName = 'NULL' if not senderName else "\'" + senderName + "\'"
    addresseeId = 'NULL' if not addresseeId else addresseeId
    addresseeName = 'NULL' if not addresseeName else "\'" + addresseeName + "\'"
    interface = 'NULL' if not interface else "\'" + interface + "\'"
    statusCode = 'NULL' if not statusCode else statusCode
    describe = 'NULL' if not describe else "\'" + describe + "\'"
    implementTime = 'NULL' if not implementTime else "\'" + implementTime + "\'"

    sql = "INSERT INTO EXECUTIONLOG VALUES (NULL,%s,%s,%s,%s,%s,%s,%s,%s,%s)" % (
        taskId, senderId, senderName, interface, addresseeId, addresseeName, statusCode,
        describe, implementTime)
    # print(sql)
    c.execute(sql)
    conn.commit()
    conn.close()
    return 1


def creat_test_data():
    add_type('CAMERA')
    add_type('LASER')

    add_device('camera01', 1, '127.0.0.1:3001')
    add_device('camera02', 1, '127.0.0.1:3002')
    add_device('camera03', 1, '127.0.0.1:3003')
    add_device('camera04', 1, '127.0.0.1:3004')
    add_device('laser01', 2, '127.0.0.1:2001')
    add_device('laser02', 2, '127.0.0.1:2002')
    add_device('laser03', 2, '127.0.0.1:5000/laser3')

    add_connection(1, 'device', 5)
    add_connection(1, 'device', 3)
    add_connection(1, 'device', 4)
    add_connection(2, 'Device', 6)
    add_connection(5, 'tYpe', 1)
    add_connection(5, 'device', 6)
    add_connection(1, 'type', 2)


# 设备分组
def show_groups():
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT * FROM GROUPS "
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    return res


def show_groups_name():
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT GROUP_ID,GROUP_NAME FROM GROUPS "
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    return res


def add_group(group_name, group_devices_list):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "INSERT INTO GROUPS  VALUES (NULL,'%s','%s')" % (str(group_name), str(group_devices_list))
    c.execute(sql)
    conn.commit()
    conn.close()
    return 1


def update_group(group_id, group_name, group_devices_list):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "UPDATE GROUPS \
               SET GROUP_NAME='%s',DEVICE_IDS='%s' \
               WHERE GROUP_ID='%s';" % (str(group_name), str(group_devices_list), str(group_id))
    c.execute(sql)
    conn.commit()
    conn.close()
    return 1


def delete_group(group_id):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "DELETE FROM GROUPS \
           WHERE GROUP_ID='%s';" % str(group_id)
    print(sql)
    c.execute(sql)
    conn.commit()
    conn.close()
    return 1


# 关系相关
# 通过设备id查找关系
def show_connection_device(device_id):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT * FROM CONNECTIONS WHERE DEVICE_ID=%s " % device_id
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    return res


def show_device_name():
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT DEVICE_ID,DEVICE_NAME FROM DEVICES "
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    return res


def show_group_device_ids(group_id):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = "SELECT * FROM GROUPS WHERE GROUP_ID=%s " % group_id
    c.execute(sql)
    res = c.fetchall()
    conn.close()
    return res


if __name__ == '__main__':
    print('test start in %s' % dbname)
    # creatTables()
    # creat_test_data()

    # print(find_addr(1))
    # print(find_connection_device(1))
    # print(find_device_in_type(2))

    # print(is_type_exist('camera'))
    # print(is_conn_exist(1, 'TYPE', 'NULL','2'))
    # print(show_type())
    # get_device_id('laser08')
    # print(get_device_info(1))
